package cn.lan.generator.utils;

import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.lan.generator.bean.EntityAttrInfo;
import cn.lan.generator.bean.EntityInfo;

public class MapperXmlFileUtil {

    public static String selectEntitySQL(EntityInfo entityInfo, String model,String dbType){
        StringBuffer buffer = new StringBuffer();
        buffer.append("\t<!-- 基本查询语句 -->\n");
        buffer.append("\t<sql id=\"select_entity_sql\">\n");
        buffer.append("\t\tselect ");
        int index =0;
        int size = entityInfo.getEntityAttrs().size();
        for (EntityAttrInfo entityAttrInfo:entityInfo.getEntityAttrs()){
            if(index != size-1){
                buffer.append("t."+getParseColumnName(entityAttrInfo.getColumnName(),dbType)+", ");
            }else{
                buffer.append("t."+entityAttrInfo.getColumnName()+" ");
            }
            if(index>0 && (index+1)%5==0){
                buffer.append("\n\t\t");
            }
            index++;
        }
        buffer.append("from "+model+" t\n");
        buffer.append("\t</sql>\n\n");
        return buffer.toString();
    }

    /**
     * where 条件
     *
     * @param entityInfo
     * @param dbType
     * @return
     */
    public static String queryWhereCond(EntityInfo entityInfo, String dbType) {
        StringBuffer buffer = new StringBuffer();
        buffer.append("\t<!-- 查询条件 -->\n");
        buffer.append("\t<sql id=\"query_where_cond\">\n");

        String primaryKey = entityInfo.getPrimaryKey();
        for (EntityAttrInfo entityAttrInfo:entityInfo.getEntityAttrs()){

            String attrName = entityAttrInfo.getAttrName();
            String columnName = entityAttrInfo.getColumnName();

            String andsql = "";
            if("String".equals(entityAttrInfo.getAttrType())){
                andsql = " and "+attrName+" != ''";
            }

            if(!StringUtil.isLikeInArray(columnName, Constant.QUERY_NOT_COND_ARRAY)){
                buffer.append("\t\t<if test=\"" + attrName + " != null"+andsql+"\"> and t." + getParseColumnName(columnName,dbType) + " = #{" + attrName + "}</if>\n");
            }

            if(attrName.equals(primaryKey)){
                buffer.append("\t\t<if test=\"" + attrName + "s != null and "+attrName+"s !=''\"> and t." + getParseColumnName(columnName,dbType) + " in (${" + attrName + "s})</if>\n");
                String noPrimaryKey = "no"+ StringUtil.toUpperCaseFirstOne(attrName);
                buffer.append("\t\t<if test=\"" + noPrimaryKey + " != null"+andsql+"\"> and t." + getParseColumnName(columnName,dbType) + " != #{" + noPrimaryKey + "}</if>\n");
            }

            if("String".equals(entityAttrInfo.getAttrType())
                && StringUtil.isLikeInArray(columnName,Constant.SQL_LIKE_NAME_ARRAY)){
                buffer.append("\t\t<if test=\"" + attrName + "Like != null and "+attrName+"Like != ''\"> and t." + getParseColumnName(columnName,dbType) + " LIKE concat('%', #{" + attrName + "Like}, '%')</if>\n");
            }

            if(ObjectUtil.isNotEmpty(columnName) && "date".equalsIgnoreCase(entityAttrInfo.getDataType())){
                buffer.append("\t\t<if test=\"" + attrName + "Start != null and "+attrName+"Start != ''\"><![CDATA[ and t."+getParseColumnName(columnName,dbType)+" >= CONCAT(#{"+attrName+"Start},' 00:00:00') ]]></if>\n");
                buffer.append("\t\t<if test=\"" + attrName + "End != null and "+attrName+"End != ''\"><![CDATA[ and t."+getParseColumnName(columnName,dbType)+" <= CONCAT(#{"+attrName+"End},' 23:59:59') ]]></if>\n");
            }

            if(ObjectUtil.isNotEmpty(columnName) && "datetime".equalsIgnoreCase(entityAttrInfo.getDataType())){
                buffer.append("\t\t<if test=\"" + attrName + "Start != null and "+attrName+"Start != ''\"><![CDATA[ and t."+getParseColumnName(columnName,dbType)+" >= #{"+attrName+"Start} ]]></if>\n");
                buffer.append("\t\t<if test=\"" + attrName + "End != null and "+attrName+"End != ''\"><![CDATA[ and t."+getParseColumnName(columnName,dbType)+" <= #{"+attrName+"End} ]]></if>\n");
            }
        }
        buffer.append("\t</sql>\n\n");
        return buffer.toString();
    }

    /**
     * SQLServer分页
     *
     * @param entityInfo
     * @return
     */
    private static String selectPageListSQLServer(EntityInfo entityInfo,String entityVo, String voPackage) {
        String sortName = getParseColumnName(entityInfo.getTablePrimaryKey(),Constant.DB_TYPE_SQLSERVER);
        for (EntityAttrInfo entityAttrInfo:entityInfo.getEntityAttrs()){
            if(Constant.DEFAULT_SORT_NAME.equals(entityAttrInfo.getColumnName())){
                sortName = Constant.DEFAULT_SORT_NAME;
                break;
            }
        }

        StringBuffer buffer = new StringBuffer();
        buffer.append("\t<!-- 分页查询 -->\n");
        buffer.append("\t<select id=\"selectListByMap\" parameterType=\"map\" resultType=\"" + voPackage+"."+entityVo + "\">\n");
        buffer.append("\t\tselect top ${pageSize} * from (\n");
        buffer.append("\t\t\tselect t.*,row_number() over(order by t." + sortName + " desc \n");
        buffer.append("\t\t\t\t) as rowNum from " + entityInfo.getTableName() + " t where 1=1 \n");
        buffer.append("\t\t<include refid=\"query_where_cond\"/>\n");
        buffer.append("\t\t) a where a.rowNum &gt; (${pageIndex}-1)*${pageSize}\n");
        buffer.append("\t</select>\n\n");
        return buffer.toString();
    }

    /**
     * MySQL分页
     *
     * @param entityInfo
     * @param entityVo
     * @param voPackage
     * @return
     */
    private static String selectPageListMySQL(EntityInfo entityInfo,String entityVo, String voPackage) {
        StringBuffer buffer = new StringBuffer();
        buffer.append("\t<!-- 分页查询 -->\n");
        buffer.append("\t<select id=\"selectListByMap\" parameterType=\"map\" resultType=\"" +voPackage+"."+ entityVo + "\">\n");
        buffer.append("\t\t<include refid=\"select_entity_sql\"/>\n");
        buffer.append("\t\t<where>\n");
        buffer.append("\t\t<include refid=\"query_where_cond\"/>\n");
        buffer.append("\t\t</where>\n");
        buffer.append("\t\t<if test=\"sortName != null and sortName != ''\">\n");
        buffer.append("\t\t\torder by t.${sortName} \n");
        buffer.append("\t\t\t<if test=\"sortOrder != null and sortOrder != ''\">${sortOrder}</if>\n");
        buffer.append("\t\t</if>\n");

        for (EntityAttrInfo entityAttrInfo:entityInfo.getEntityAttrs()){
            if(Constant.DEFAULT_SORT_NAME.equals(entityAttrInfo.getColumnName())){
                buffer.append("\t\t<if test=\"sortName == null or sortName ==''\">\n");
                buffer.append("\t\t\torder by t.create_time desc \n");
                buffer.append("\t\t</if>\n");
                break;
            }
        }

        buffer.append("\t\t<if test=\"pageIndex != null and pageSize != null\">\n");
        buffer.append("\t\t\tlimit ${(pageIndex-1)*pageSize },${pageSize}\n");
        buffer.append("\t\t</if>\n");
        buffer.append("\t</select>\n\n");
        return buffer.toString();
    }

    /**
     * MySQL分页
     *
     * @param entityInfo
     * @return
     */
    public static String selectPageList(EntityInfo entityInfo,String entityVo, String voPackage,String dbType,String model) {
        String pageListSQL = "";
        if (dbType.equals(Constant.DB_TYPE_MYSQL)) {
            pageListSQL = MapperXmlFileUtil.selectPageListMySQL(entityInfo,entityVo,voPackage);
        } else if (dbType.equals(Constant.DB_TYPE_SQLSERVER)) {
            pageListSQL = MapperXmlFileUtil.selectPageListSQLServer(entityInfo,entityVo,voPackage);
        } else if (dbType.equals(Constant.DB_TYPE_DB2)) {
            pageListSQL = MapperXmlFileUtil.selectPageListDB2(entityInfo, model,entityVo,voPackage);
        }
        return pageListSQL;
    }

    /**
     * DB2分页
     *
     * @param entityInfo
     * @param model
     * @return
     */
    private static String selectPageListDB2(EntityInfo entityInfo, String model, String entityVo, String voPackage) {
        String sortName = getParseColumnName(entityInfo.getTablePrimaryKey(),Constant.DB_TYPE_DB2);
        for (EntityAttrInfo entityAttrInfo:entityInfo.getEntityAttrs()){
            if(Constant.DEFAULT_SORT_NAME.equals(entityAttrInfo.getColumnName())){
                sortName = Constant.DEFAULT_SORT_NAME;
                break;
            }
        }
        StringBuffer buffer = new StringBuffer();
        buffer.append("\t<!-- 分页查询 -->\n");
        buffer.append("\t<select id=\"selectListByMap\" parameterType=\"map\" resultType=\"" + voPackage+"."+ entityVo + "\">\n");
        buffer.append("\t\tselect * from (\n");
        buffer.append("\t\t\tselect t.*,row_number() over(order by t." + sortName + " desc \n");
        buffer.append("\t\t\t\t) as rowNum from " + model + " t where 1=1 \n");
        buffer.append("\t\t<include refid=\"query_where_cond\"/>\n");
        buffer.append("\t\t) a where a.rowNum &gt; (${pageIndex}-1)*${pageSize} and a.rowNum &lt;= ${pageIndex}*${pageSize} \n");
        buffer.append("\t</select>\n\n");
        return buffer.toString();
    }

    /**
     * 查询总数SQL
     *
     * @param model
     * @param model
     * @return
     */
    public static String selectListCountSQL(String model) {
        StringBuffer buffer = new StringBuffer();
        buffer.append("\t<!-- 查询总数 -->\n");
        buffer.append("\t<select id=\"selectCountByMap\" parameterType=\"map\" resultType=\"long\">\n");
        buffer.append("\t\tselect count(1) from " + model + " t where 1=1 \n");
        buffer.append("\t\t<include refid=\"query_where_cond\"/>\n");
        buffer.append("\t</select>\n\n");
        return buffer.toString();
    }

    /**
     * 查询单个SQL
     *
     * @param entityInfo
     * @param voPackage
     * @return
     */
    public static String selectByIdSQL(EntityInfo entityInfo, String voPackage,String entityVo,String dbType) {
        StringBuffer buffer = new StringBuffer();
        buffer.append("\t<!-- 根据ID查询 -->\n");
        buffer.append("\t<select id=\"selectById\" parameterType=\"" + entityInfo.getPrimaryKeyType() + "\" resultType=\"" + voPackage+"."+entityVo + "\">\n");
        buffer.append("\t\t<include refid=\"select_entity_sql\"/>\n");
        buffer.append("\t\twhere t." + getParseColumnName(entityInfo.getTablePrimaryKey(),dbType) + " = #{" + entityInfo.getPrimaryKey() + "}\n");
        buffer.append("\t</select>\n\n");
        return buffer.toString();
    }

    /**
     * 查询所有SQL
     *
     * @param entityVo
     * @param voPackage
     * @return
     */
    public static String selectAllListSQL(String entityVo, String voPackage) {
        StringBuffer buffer = new StringBuffer();
        buffer.append("\t<!-- 查询所有 -->\n");
        buffer.append("\t<select id=\"selectAllList\" parameterType=\"map\" resultType=\"" +voPackage+"."+ entityVo + "\">\n");
        buffer.append("\t\t<include refid=\"select_entity_sql\"/>\n");
        buffer.append("\t\t<where>\n");
        buffer.append("\t\t<include refid=\"query_where_cond\"/>\n");
        buffer.append("\t\t</where>\n");
        buffer.append("\t\t<if test=\"sortName != null and sortName != ''\">\n");
        buffer.append("\t\t\torder by t.${sortName} \n");
        buffer.append("\t\t\t<if test=\"sortOrder != null and sortOrder != ''\">\n");
        buffer.append("\t\t\t\t${sortOrder}\n");
        buffer.append("\t\t\t</if>\n");
        buffer.append("\t\t</if>\n");
        buffer.append("\t</select>\n\n");
        return buffer.toString();
    }

    /**
     * 删除SQL
     *
     * @param entityInfo
     * @param model
     * @return
     * @throws Exception
     */
    public static String deleteSQL(EntityInfo entityInfo, String model,String dbType) throws Exception {
        StringBuffer buffer = new StringBuffer();
        buffer.append("\t<!-- 删除 -->\n");
        buffer.append("\t<delete id=\"deleteByMap\" parameterType=\"map\">\n");
        buffer.append("\t\tdelete t from " + model + " t where 1=1\n");
        buffer.append("\t\t<include refid=\"query_where_cond\"/>\n");
        buffer.append("\t</delete>\n\n");
        return buffer.toString();
    }

    /**
     * 修改SQLByMap
     *
     * @param entityInfo
     * @param model
     * @return
     * @throws Exception
     */
    public static String updateSQLByMap(EntityInfo entityInfo, String model,String dbType) throws Exception {
        StringBuffer buffer = new StringBuffer();
        buffer.append("\t<!-- 修改ByMap -->\n");
        buffer.append("\t<update id=\"updateByMap\" parameterType=\"map\">\n");
        buffer.append("\t\tupdate " + model + " t \n");
        buffer.append("\t\t<set>\n");
        buffer.append(updateAttributeByMap(entityInfo,dbType));
        buffer.append("\t\t</set>\n");
        buffer.append("\t\twhere 1=1\n");
        buffer.append("\t\t<include refid=\"query_where_cond\"/>\n");
        buffer.append("\t</update>\n\n");
        return buffer.toString();
    }

    /**
     * 修改SQL语句拼接属性
     *
     * @return
     * @throws Exception
     */
    private static String updateAttributeByMap(EntityInfo entityInfo,String dbType) {
        StringBuffer sb = new StringBuffer();
        for (EntityAttrInfo entityAttrInfo:entityInfo.getEntityAttrs()){
            String attrName = entityAttrInfo.getAttrName();
            String columnName = entityAttrInfo.getColumnName();
            if (!columnName.equals(entityInfo.getTablePrimaryKey())) {
                sb.append("\t\t\t<if test=\"" + attrName + "New != null\"> t." + getParseColumnName(columnName,dbType) + " = #{" + attrName + "New},</if>\n");
            }
        }
        return sb.toString();
    }

    public static String getParseColumnName(String columnName,String dbTyoe){
        String colName = columnName;
        for (String keyName:Constant.SQL_KEYS){
            if(keyName.equals(columnName.toLowerCase())){
                if(Constant.DB_TYPE_DB2.equals(dbTyoe)){
                    colName= "\""+columnName+"\"";
                }else if(Constant.DB_TYPE_MYSQL.equals(dbTyoe)){
                    colName= "`"+columnName+"`";
                }else if(Constant.DB_TYPE_SQLSERVER.equals(dbTyoe)){
                    colName= "\""+columnName+"\"";
                }
                break;
            }
        }
        return colName;
    }
}
